﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using MAPDavy.Entities;

namespace MAPDavy.WebMVC3.Models
{
    public class CRFTemplateExcel
    {
        private static List<CRFTemplate> _crfTemplates;

        public static byte[] CreateExcel(List<CRFTemplate> crfTemplates)
        {
            var memoryStream = new MemoryStream();
            
            using (var excel = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook, true))
            {
                _crfTemplates = crfTemplates;

                CreateParts(excel);
            }

            return memoryStream.ToArray();
        }

        public static void CreateParts(SpreadsheetDocument excel)
        {
            //workbook area
            var workbookPart = excel.AddWorkbookPart();
            //workbook content
            CreateWorkbookPartContent(workbookPart);

            //worksheet area
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetContent(worksheetPart);
        }

        private static void CreateWorkbookPartContent(WorkbookPart workbookPart)
        {
            var workbook = new Workbook();

            //Workbook sheets
            var sheets = new Sheets();
            var sheet = new Sheet { Name = "CRF Template", SheetId = 1, Id = "rId1" };

            sheets.Append(sheet);
            workbook.Append(sheets);

            workbookPart.Workbook = workbook;
        }

        private static void GenerateWorksheetContent(WorksheetPart worksheetPart)
        {
            var worksheet = new Worksheet();

            var sheetData = new SheetData();

            var rowNumber = "1";

            var row = new Row { RowIndex = UInt32.Parse(rowNumber) };

            var TermName = new Cell { CellReference = "A" + rowNumber, CellValue = new CellValue("Term Name"), DataType = CellValues.String };
            row.Append(TermName);

            var BeginningInstructionalDate = new Cell { CellReference = "B" + rowNumber, CellValue = new CellValue("Beginning Instructional Date"), DataType = CellValues.String };
            row.Append(BeginningInstructionalDate);

            var TotalNumberOfInstructionalDays = new Cell { CellReference = "C" + rowNumber, CellValue = new CellValue("Total Number Of Instructional Days"), DataType = CellValues.String };
            row.Append(TotalNumberOfInstructionalDays);

            var TestingStartDate = new Cell { CellReference = "D" + rowNumber, CellValue = new CellValue("Testing Start Date"), DataType = CellValues.String };
            row.Append(TestingStartDate);

            var TestingEndDate = new Cell { CellReference = "E" + rowNumber, CellValue = new CellValue("Testing End Date"), DataType = CellValues.String };
            row.Append(TestingEndDate);

            var SchoolName = new Cell { CellReference = "F" + rowNumber, CellValue = new CellValue("School Name"), DataType = CellValues.String };
            row.Append(SchoolName);

            var SchoolNumber = new Cell { CellReference = "G" + rowNumber, CellValue = new CellValue("School Number"), DataType = CellValues.String };
            row.Append(SchoolNumber);

            var PreviousTeacherID = new Cell { CellReference = "H" + rowNumber, CellValue = new CellValue("Previous Teacher ID"), DataType = CellValues.String };
            row.Append(PreviousTeacherID);

            var TeacherID = new Cell { CellReference = "I" + rowNumber, CellValue = new CellValue("Teacher ID"), DataType = CellValues.String };
            row.Append(TeacherID);

            var TeacherLastName = new Cell { CellReference = "J" + rowNumber, CellValue = new CellValue("Teacher Last Name"), DataType = CellValues.String };
            row.Append(TeacherLastName);

            var TeacherFirstName = new Cell { CellReference = "K" + rowNumber, CellValue = new CellValue("Teacher First Name"), DataType = CellValues.String };
            row.Append(TeacherFirstName);

            var TeacherMiddleInitial = new Cell { CellReference = "L" + rowNumber, CellValue = new CellValue("Teacher Middle Initial"), DataType = CellValues.String };
            row.Append(TeacherMiddleInitial);

            var ClassName = new Cell { CellReference = "M" + rowNumber, CellValue = new CellValue("Class Name"), DataType = CellValues.String };
            row.Append(ClassName);

            var ClassPeriodName = new Cell { CellReference = "N" + rowNumber, CellValue = new CellValue("Class Period Name"), DataType = CellValues.String};
            row.Append(ClassPeriodName);

            var ClassSubjectName = new Cell { CellReference = "O" + rowNumber, CellValue = new CellValue("Class Subject Name"), DataType = CellValues.String };
            row.Append(ClassSubjectName);

            var PreviousStudentID = new Cell { CellReference = "P" + rowNumber, CellValue = new CellValue("Previous Student ID"), DataType = CellValues.Number };
            row.Append(PreviousStudentID);

            var StudentId = new Cell { CellReference = "Q" + rowNumber, CellValue = new CellValue("Student Id"), DataType = CellValues.Number };
            row.Append(StudentId);

            var StudentLastName = new Cell { CellReference = "R" + rowNumber, CellValue = new CellValue("Student Last Name"), DataType = CellValues.String };
            row.Append(StudentLastName);

            var StudentFirstName = new Cell { CellReference = "S" + rowNumber, CellValue = new CellValue("Student First Name"), DataType = CellValues.String };
            row.Append(StudentFirstName);

            var StudentMiddleInitial = new Cell { CellReference = "T" + rowNumber, CellValue = new CellValue("Student Middle Initial"), DataType = CellValues.String };
            row.Append(StudentMiddleInitial);

            var StudentDateOfBirth = new Cell { CellReference = "U" + rowNumber, CellValue = new CellValue("Student Date Of Birth"), DataType = CellValues.Date };
            row.Append(StudentDateOfBirth);

            var StudentGender = new Cell { CellReference = "V" + rowNumber, CellValue = new CellValue("Student Gender"), DataType = CellValues.String };
            row.Append(StudentGender);

            var StudentGrade = new Cell { CellReference = "W" + rowNumber, CellValue = new CellValue("Student Grade"), DataType = CellValues.String };
            row.Append(StudentGrade);

            var StudentEthnicGroupName = new Cell { CellReference = "X" + rowNumber, CellValue = new CellValue("Student Ethnic GroupName"), DataType = CellValues.String };
            row.Append(StudentEthnicGroupName);

            var StudentLanguagePreferenceName = new Cell { CellReference = "Y" + rowNumber, CellValue = new CellValue("Student Language Preference Name"), DataType = CellValues.String };
            row.Append(StudentLanguagePreferenceName);

            sheetData.Append(row);

            for (var i = 0; i < _crfTemplates.Count; i++)
            {
                 rowNumber = (i + 2).ToString();

                 row = new Row { RowIndex = UInt32.Parse(rowNumber) };

                 TermName = new Cell { CellReference = "A" + rowNumber,CellValue = new CellValue(_crfTemplates[i].TermName),DataType = CellValues.String};
                row.Append(TermName);

                 BeginningInstructionalDate = new Cell { CellReference = "B" + rowNumber, CellValue = new CellValue(_crfTemplates[i].BeginningInstructionalDate), DataType = CellValues.Date };
                row.Append(BeginningInstructionalDate);

                 TotalNumberOfInstructionalDays = new Cell { CellReference = "C" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TotalNumberOfInstructionalDays.ToString()), DataType = CellValues.Number };
                row.Append(TotalNumberOfInstructionalDays);

                TestingStartDate = new Cell { CellReference = "D" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TestingStartDate), DataType = CellValues.Date };
                row.Append(TestingStartDate);

                 TestingEndDate = new Cell { CellReference = "E" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TestingEndDate), DataType = CellValues.Date };
                row.Append(TestingEndDate);

                 SchoolName = new Cell { CellReference = "F" + rowNumber, CellValue = new CellValue(_crfTemplates[i].SchoolName), DataType = CellValues.String };
                row.Append(SchoolName);

                 SchoolNumber = new Cell { CellReference = "G" + rowNumber, CellValue = new CellValue(_crfTemplates[i].SchoolNumber), DataType = CellValues.Number };
                row.Append(SchoolNumber);

                 PreviousTeacherID = new Cell { CellReference = "H" + rowNumber, CellValue = new CellValue(_crfTemplates[i].PreviousTeacherID), DataType = CellValues.String };
                row.Append(PreviousTeacherID);

                 TeacherID = new Cell { CellReference = "I" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TeacherID), DataType = CellValues.String };
                row.Append(TeacherID);

                 TeacherLastName = new Cell { CellReference = "J" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TeacherLastName), DataType = CellValues.String };
                row.Append(TeacherLastName);

                 TeacherFirstName = new Cell { CellReference = "K" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TeacherFirstName), DataType = CellValues.String };
                row.Append(TeacherFirstName);

                 TeacherMiddleInitial = new Cell { CellReference = "L" + rowNumber, CellValue = new CellValue(_crfTemplates[i].TeacherMiddleInitial), DataType = CellValues.String };
                row.Append(TeacherMiddleInitial);

                 ClassName = new Cell { CellReference = "M" + rowNumber, CellValue = new CellValue(_crfTemplates[i].ClassName), DataType = CellValues.String };
                row.Append(ClassName);

                 ClassPeriodName = new Cell { CellReference = "N" + rowNumber, CellValue = new CellValue(_crfTemplates[i].ClassPeriodName), DataType = CellValues.Number, };
                row.Append(ClassPeriodName);

                 ClassSubjectName = new Cell { CellReference = "O" + rowNumber, CellValue = new CellValue(_crfTemplates[i].ClassSubjectName), DataType = CellValues.String };
                row.Append(ClassSubjectName);

                 PreviousStudentID = new Cell { CellReference = "P" + rowNumber, CellValue = new CellValue(_crfTemplates[i].PreviousStudentID), DataType = CellValues.Number };
                row.Append(PreviousStudentID);

                 StudentId = new Cell { CellReference = "Q" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentId), DataType = CellValues.Number };
                row.Append(StudentId);

                 StudentLastName = new Cell { CellReference = "R" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentLastName), DataType = CellValues.String };
                row.Append(StudentLastName);

                 StudentFirstName = new Cell { CellReference = "S" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentFirstName), DataType = CellValues.String };
                row.Append(StudentFirstName);

                 StudentMiddleInitial = new Cell { CellReference = "T" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentMiddleInitial), DataType = CellValues.String };
                row.Append(StudentMiddleInitial);

                 StudentDateOfBirth = new Cell { CellReference = "U" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentDateOfBirth), DataType = CellValues.Date };
                row.Append(StudentDateOfBirth);

                 StudentGender = new Cell { CellReference = "V" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentGender), DataType = CellValues.String };
                row.Append(StudentGender);

                 StudentGrade = new Cell { CellReference = "W" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentGrade), DataType = CellValues.String };
                row.Append(StudentGrade);

                 StudentEthnicGroupName = new Cell { CellReference = "X" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentEthnicGroupName), DataType = CellValues.String };
                row.Append(StudentEthnicGroupName);

                StudentLanguagePreferenceName = new Cell { CellReference = "Y" + rowNumber, CellValue = new CellValue(_crfTemplates[i].StudentLanguagePreferenceName), DataType = CellValues.String };
                row.Append(StudentLanguagePreferenceName);
                
                sheetData.Append(row);
            }

            worksheet.Append(sheetData);

            worksheetPart.Worksheet = worksheet;
        }
    }
}